﻿using Blm.PgsqlDriver.Helpers;
using Blm.Utils.Extensions;
using Language.Resource;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Localization;
using Newtonsoft.Json;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using VisionCloud.Core.Model.Ms;
using VisionCloud.Domain.Common;
using VisionCloud.Domain.Data;
using VisionCloud.Domain.DtoModel.Ms;
using VisionCloud.Domain.DtoModel.Rs;
using VisionCloud.Domain.DtoModel.Sys;
using VisionCloud.Domain.Extension;
using VisionCloud.Domain.Repository.Interface.Ms;

namespace VisionCloud.Domain.Repository.Implement.Ms
{
    public class MsProjectRepository : BaseRepository<MsProject>, IMsProjectRepository
    {
        public IStringLocalizer<RepositoryResource> Localizer { get; set; }

        public async Task<List<MsProject>> GetDataByProjects(Guid[] projectIds)
        {
            return await (from c in Db.MsProjects where projectIds.Contains(c.ProjectId) && c.IsDeleted == false select c).AsNoTracking().ToListAsync().ConfigureAwait(false);
        }

        /// <summary>
        /// 获取SQL
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="isQueryTotal"></param>
        /// <returns></returns>
        private (string, List<NpgsqlParameter>) GetPageSQL(MsProjectCondition condition, bool isQueryTotal)
        {
            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>();
            if (!string.IsNullOrEmpty(condition.ProjectNumOrName))
                parameters.Add(new NpgsqlParameter("@name", $"%{condition.ProjectNumOrName.Replace("%", "\\%")}%"));

            var user = OperatorProvider.Get();
            var selectSql = isQueryTotal ? "count(*)" : "*";
            var index = Math.Max(condition.CurrentPage - 1, 0) * condition.PageSize;
            var pageSql = isQueryTotal ? "" : $" OFFSET {index} limit {condition.PageSize} ";
            string orderbySql = string.Empty;
            if (!isQueryTotal)
            {
                var ascdesc = condition.AscDesc.ToLower() == "desc" ? "desc" : "asc";
                var orderByStr = "\"" + condition.OrderBy.Replace("DESC", "").Replace("ASC", "").Replace(" ", "") + "\"";
                orderbySql = $" order by {orderByStr} {ascdesc}";
            }
            //var orderbySql = isQueryTotal ? "" : $@" order by ""{condition.OrderBy.Replace("DESC", "").Replace("ASC", "").Trim()}"" {condition.AscDesc}";
            var whereSql = string.IsNullOrEmpty(condition.ProjectNumOrName) ? "" : @" where (""Name"" like @name or ""ProjectNo"" like @name or ""UserNames"" like @name or ""BuildingCount"":: text like @name or ""ShowAddress"" like @name) ";
            string sql;
            if (user.IsCompanyAdmin)
            {
                whereSql += @$" {(string.IsNullOrEmpty(whereSql) ? "where" : "and")} ""CompanyGuid"" = '{user.CompanyGuid}'";

                sql = @$"with tb as (
                        SELECT a.""ProjectId"",a.""CompanyGuid"",a.""Name"",a.""Province"",a.""City"",a.""Town"",a.""Address"",
                        (a.""Province"" || (case when a.""City"" is null then '' else '-' end) || COALESCE(a.""City"", '')
                        || (case when a.""Town"" is null then '' else '-' end) || COALESCE(a.""Town"", '')) ""ShowAddress"",
                        a.""Stage"",a.""Standard"",a.""Description"",a.""Details"",a.""CreatedBy"",a.""CreatedDate"",a.""LastUpdatedBy"",
                        a.""LastUpdatedDate"",a.""ProjectNo"",a.""IsPullByClient"",a.""IsCanPull"",a.""PubliceDetailsCount"",
                        a.""PubliceDate"",a.""RegionalCompany"", a.""ProjectManager"",
                        jsonb_array_length(a.""Details"" :: jsonb) ""BuildingCount"",d.""UserNames""
                        FROM ""MsProject"" a
                        left join(
                        select b.""YunProjectGuid"", string_agg(c.""Name"", ',') ""UserNames""
                        from ""MsUserToProject"" b inner join ""SysUser"" c on b.""YunUserGuid"" = c.""YunUserGuid""
                         where c.""IsDeleted"" = false and c.""Status"" = 1 GROUP BY b.""YunProjectGuid""
                        ) d on a.""ProjectId"" = d.""YunProjectGuid""
                        where a.""IsDeleted"" = FALSE and a.""Status"" = 0
                        ) select {selectSql} from tb {whereSql} {orderbySql} {pageSql}";
            }
            else
            {
                whereSql += user.RoleCode.Contains("cloud_measure_robot_master") ? "" : @$" {(string.IsNullOrEmpty(whereSql) ? "where" : "and")} ""YunUserGuid"" = '{user.Id}' ";
                whereSql += !(user.RoleNameList.Contains("测量员") || user.RoleNameList.Contains("项目管理员")) ? "" :
                    @$" {(string.IsNullOrEmpty(whereSql) ? "where" : "and")} ""PubliceDate"" is not null ";

                sql = @$"with tb as (
                        SELECT a.""ProjectId"",a.""CompanyGuid"",a.""Name"",a.""Province"",a.""City"",a.""Town"",a.""Address"",
                        (a.""Province"" || (case when a.""City"" is null then '' else '-' end) || COALESCE(a.""City"", '')
                        || (case when a.""Town"" is null then '' else '-' end) || COALESCE(a.""Town"", '')) ""ShowAddress"",
                        a.""Stage"",a.""Standard"",a.""Description"",a.""Details"",a.""CreatedBy"",
                        a.""CreatedDate"",a.""LastUpdatedBy"",a.""LastUpdatedDate"",a.""ProjectNo"",a.""IsPullByClient"",
                        a.""IsCanPull"",a.""PubliceDetailsCount"",a.""PubliceDate"",a.""RegionalCompany"", a.""ProjectManager"",
                        jsonb_array_length(a.""Details"" :: jsonb) ""BuildingCount"",d.""UserNames"",e.""YunUserGuid"" FROM ""MsProject"" a
                        left join(
                        select b.""YunProjectGuid"", string_agg(c.""Name"", ',') ""UserNames""
                        from ""MsUserToProject"" b inner join ""SysUser"" c on b.""YunUserGuid"" = c.""YunUserGuid""
                        where c.""IsDeleted"" = false and c.""Status"" = 1 GROUP BY b.""YunProjectGuid""
                        ) d on a.""ProjectId"" = d.""YunProjectGuid""
                        inner join ""MsUserToProject"" e on a.""ProjectId"" = e.""YunProjectGuid""
                        where a.""IsDeleted"" = FALSE and a.""Status"" = 0
                        and a.""CompanyGuid"" = '{user.CompanyGuid}'
                    ) select {selectSql} from tb {whereSql} {orderbySql} {pageSql}";
            }
            return (sql, parameters);
        }

        /// <summary>
        /// 获取项目总数量
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private async ValueTask<long> GetProjectTotal(MsProjectCondition condition)
        {
            var (sql, paras) = GetPageSQL(condition, true);

            var total = await PgSqlHelper.QueryScalarAsync<long>(CommandType.Text, sql, false, paras.ToArray());

            return total;
        }

        /// <summary>
        /// 获取项目列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private async Task<List<MsProjectDto>> GetProjectDtoList(MsProjectCondition condition)
        {
            var (sql, paras) = GetPageSQL(condition, false);

            var list = await PgSqlHelper.QueryListAsync<MsProjectDto>(CommandType.Text, sql, false, paras.ToArray());

            return list;
        }

        /// <summary>
        /// 分页获取项目列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetPage(MsProjectCondition condition)
        {
            condition.AscDesc = string.IsNullOrEmpty(condition.OrderBy) ? "DESC" : condition.AscDesc;
            condition.OrderBy = string.IsNullOrEmpty(condition.OrderBy) ? "ProjectNo DESC" : $"{condition.OrderBy} {condition.AscDesc}";

            var total = await GetProjectTotal(condition);
            var list = await GetProjectDtoList(condition);

            var result = condition.CopyTo<ApiPageResult>();
            result.PageData = list;
            result.Total = total;

            List<UserInfo> userList = new List<UserInfo>();
            if (list.Count > 0)
            {
                var projectIdArr = list.Select(p => p.ProjectId).ToArray();
                userList = await (from userproject in Db.MsUserToProjects
                                  join user in Db.SysUsers on userproject.YunUserGuid equals user.YunUserGuid
                                  where projectIdArr.Contains(userproject.YunProjectGuid) && !user.IsDeleted && user.Status == Core.Model.Sys.SysUser.EnumUserStatus.正常
                                  select new UserInfo
                                  {
                                      ProjectId = userproject.YunProjectGuid,
                                      UserId = userproject.YunUserGuid,
                                      Name = user.Name,
                                      Phone = user.PhoneNumber,
                                      Position = user.Position,
                                      Department = user.Department,
                                      Email = user.Email,
                                      RoleName = user.RoleNameList
                                  }).ToListAsync().ConfigureAwait(false);
            }

            foreach (var project in result.PageData as List<MsProjectDto>)
            {
                if (!string.IsNullOrEmpty(project.Details))
                {
                    project.DetailsInfo = JsonConvert.DeserializeObject<List<Detail>>(project.Details);
                    //测量员和项目管理员只能到发布的楼层信息。删除未发布的楼层
                    if (OperatorProvider.Get().RoleNameList.Contains("测量员") || OperatorProvider.Get().RoleNameList.Contains("项目管理员"))
                    {
                        project.DetailsInfo.RemoveRange(project.PubliceDetailsCount, project.DetailsInfo.Count() - project.PubliceDetailsCount);
                    }
                    project.BuildingCount = project.DetailsInfo.Count().ToString();
                }
                //项目相关人员Id
                project.Users = userList?.Where(p => p.ProjectId == project.ProjectId).OrderBy(x => x.Name).ToList();
            }
            return result;
        }

        /// <summary>
        /// 获取项目列表的IQueryable
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private IQueryable<MsProjectDto> GetListIQueryable(MsProjectCondition condition)
        {
            var user = OperatorProvider.Get();
            IQueryable<MsProjectDto> queryable;
            if (user.IsCompanyAdmin)
            {
                queryable = (
                from project in Db.MsProjects
                where (!project.IsDeleted && project.Status == MsProject.ProjectStatus.启用
                && (project.CompanyGuid == user.CompanyGuid)
                && (string.IsNullOrEmpty(condition.ProjectName) || project.Name.Contains(condition.ProjectName))
                && (
                    string.IsNullOrEmpty(condition.ProjectNumOrName)
                    ||
                    (project.Name.Contains(condition.ProjectNumOrName) || project.ProjectNo.Contains(condition.ProjectNumOrName)))
                   && (condition.ProjectId == Guid.Empty || project.ProjectId == condition.ProjectId)
                   && (string.IsNullOrEmpty(condition.Standard) || project.Standard.Contains(condition.Standard))
                   && (string.IsNullOrEmpty(condition.Stage) || project.Stage.Contains(condition.Stage))
                   && (string.IsNullOrEmpty(condition.Province) || project.Province.Contains(condition.Province))
                   && (string.IsNullOrEmpty(condition.City) || project.City.Contains(condition.City))
                   && (string.IsNullOrEmpty(condition.Town) || project.Town.Contains(condition.Town))
                   && (condition.BeginDate == DateTime.MinValue || project.CreatedDate > condition.BeginDate)
                   && (condition.EndDate == DateTime.MinValue || project.CreatedDate > condition.EndDate)
                   && (string.IsNullOrEmpty(condition.ProjectNum) || project.ProjectNo.Contains(condition.ProjectNum))
                   )
                select new MsProjectDto()
                {
                    ProjectId = project.ProjectId,
                    Name = project.Name,
                    Province = project.Province,
                    City = project.City,
                    Town = project.Town,
                    ShowAddress = project.Province
                                  + (string.IsNullOrEmpty(project.City) ? "" : "-" + project.City)
                                    + (string.IsNullOrEmpty(project.Town) ? "" : "-" + project.Town),
                    Address = project.Address,
                    Stage = project.Stage,
                    Standard = project.Standard,
                    Description = project.Description,
                    Details = project.Details,
                    CreatedBy = project.CreatedBy,
                    CreatedDate = project.CreatedDate,
                    LastUpdatedDate = project.LastUpdatedDate,
                    ProjectNo = project.ProjectNo,
                    IsPullByClient = project.IsPullByClient,
                    IsCanPull = project.IsCanPull,
                    PubliceDetailsCount = project.PubliceDetailsCount,
                    PubliceDate = project.PubliceDate,
                    RegionalCompany = project.RegionalCompany,
                    ProjectManager = project.ProjectManager,
                });
            }
            else
            {
                queryable = (
                from project in Db.MsProjects
                join userprojet in Db.MsUserToProjects on project.ProjectId equals userprojet.YunProjectGuid
                where (!project.IsDeleted && project.Status == MsProject.ProjectStatus.启用
                && (userprojet.YunUserGuid == user.Id || user.RoleCode.Contains("cloud_measure_robot_master"))
                && (string.IsNullOrEmpty(condition.ProjectName) || project.Name.Contains(condition.ProjectName))
                && (
                    string.IsNullOrEmpty(condition.ProjectNumOrName)
                    ||
                    (project.Name.Contains(condition.ProjectNumOrName) || project.ProjectNo.Contains(condition.ProjectNumOrName)))
                   && (condition.ProjectId == Guid.Empty || project.ProjectId == condition.ProjectId)
                   && (string.IsNullOrEmpty(condition.Standard) || project.Standard.Contains(condition.Standard))
                   && (string.IsNullOrEmpty(condition.Stage) || project.Stage.Contains(condition.Stage))
                   && (string.IsNullOrEmpty(condition.Province) || project.Province.Contains(condition.Province))
                   && (string.IsNullOrEmpty(condition.City) || project.City.Contains(condition.City))
                   && (string.IsNullOrEmpty(condition.Town) || project.City.Contains(condition.Town))
                   && (condition.BeginDate == DateTime.MinValue || project.CreatedDate > condition.BeginDate)
                   && (condition.EndDate == DateTime.MinValue || project.CreatedDate > condition.EndDate)
                   && (string.IsNullOrEmpty(condition.ProjectNum) || project.ProjectNo.Contains(condition.ProjectNum))
                   //如果是测量员，只能看已发布的项目
                   && (!(user.RoleNameList.Contains("测量员") || user.RoleNameList.Contains("项目管理员")) ? true : (project.PubliceDate.HasValue)))
                select new MsProjectDto()
                {
                    ProjectId = project.ProjectId,
                    Name = project.Name,
                    Province = project.Province,
                    City = project.City,
                    Town = project.Town,
                    ShowAddress = project.Province
                                  + (string.IsNullOrEmpty(project.City) ? "" : "-" + project.City)
                                  + (string.IsNullOrEmpty(project.Town) ? "" : "-" + project.Town),
                    Address = project.Address,
                    Stage = project.Stage,
                    Standard = project.Standard,
                    Description = project.Description,
                    Details = project.Details,
                    CreatedBy = project.CreatedBy,
                    CreatedDate = project.CreatedDate,
                    LastUpdatedDate = project.LastUpdatedDate,
                    ProjectNo = project.ProjectNo,
                    IsPullByClient = project.IsPullByClient,
                    IsCanPull = project.IsCanPull,
                    PubliceDetailsCount = project.PubliceDetailsCount,
                    PubliceDate = project.PubliceDate,
                    RegionalCompany = project.RegionalCompany,
                    ProjectManager = project.ProjectManager,                     // 项目经理
                }
                );
            }

            return queryable;
        }

        /// <summary>
        /// 获取项目列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetList(MsProjectCondition condition)
        {
            var selectQueryable = GetListIQueryable(condition);
            condition.AscDesc = string.IsNullOrEmpty(condition.OrderBy) ? "DESC" : condition.AscDesc;
            condition.OrderBy = string.IsNullOrEmpty(condition.OrderBy) ? "ProjectNo DESC" : $"{condition.OrderBy} {condition.AscDesc}";
            var result = condition.CopyTo<ApiPageResult>();
            result.PageData = await selectQueryable.ToPageList(condition);
            result.Total = condition.Total;

            foreach (var projectList in result.PageData as List<MsProjectDto>)
            {
                if (!string.IsNullOrEmpty(projectList.Details))
                {
                    projectList.DetailsInfo = JsonConvert.DeserializeObject<List<Detail>>(projectList.Details);
                    //测量员和项目管理员只能到发布的楼层信息。删除未发布的楼层
                    if (OperatorProvider.Get().RoleNameList.Contains("测量员") || OperatorProvider.Get().RoleNameList.Contains("项目管理员"))
                    {
                        projectList.DetailsInfo.RemoveRange(projectList.PubliceDetailsCount, projectList.DetailsInfo.Count() - projectList.PubliceDetailsCount);
                    }
                    projectList.BuildingCount = projectList.DetailsInfo.Count().ToString();
                }
                //项目相关人员Id
                projectList.Users = new List<UserInfo>();
                var userList = await (from userproject in Db.MsUserToProjects
                                      join user in Db.SysUsers on userproject.YunUserGuid equals user.YunUserGuid
                                      where userproject.YunProjectGuid == projectList.ProjectId && !user.IsDeleted && user.Status == Core.Model.Sys.SysUser.EnumUserStatus.正常
                                      select new UserInfo
                                      {
                                          UserId = userproject.YunUserGuid,
                                          Name = user.Name,
                                          Phone = user.PhoneNumber,
                                          Position = user.Position,
                                          Department = user.Department,
                                          Email = user.Email,
                                          RoleName = user.RoleNameList
                                      }).ToArrayAsync().ConfigureAwait(false);
                foreach (var i in userList)
                {
                    var j = i.CopyTo<UserInfo>();
                    projectList.Users.Add(j);
                    projectList.UserNames = (string.IsNullOrEmpty(projectList.UserNames) ? "" : (projectList.UserNames + ",")) + j.Name;
                }
            }
            return result;
        }

        /// <summary>
        /// 通过项目Id获取项目详情
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetProjectById(MsProjectCondition condition)
        {
            var selectQueryable = GetListIQueryable(condition);
            condition.AscDesc = string.IsNullOrEmpty(condition.OrderBy) ? "DESC" : condition.AscDesc;
            condition.OrderBy = string.IsNullOrEmpty(condition.OrderBy) ? "ProjectNo DESC" : $"{condition.OrderBy} {condition.AscDesc}";
            var result = condition.CopyTo<ApiPageResult>();
            result.PageData = await selectQueryable.ToPageList(condition);
            result.Total = condition.Total;

            foreach (var projectList in result.PageData as List<MsProjectDto>)
            {
                if (!string.IsNullOrEmpty(projectList.Details))
                {
                    projectList.DetailsInfo = JsonConvert.DeserializeObject<List<Detail>>(projectList.Details);
                    //测量员和项目管理员只能到发布的楼层信息。删除未发布的楼层
                    if (OperatorProvider.Get().RoleNameList.Contains("测量员") || OperatorProvider.Get().RoleNameList.Contains("项目管理员"))
                    {
                        projectList.DetailsInfo.RemoveRange(projectList.PubliceDetailsCount, projectList.DetailsInfo.Count() - projectList.PubliceDetailsCount);
                    }
                    projectList.BuildingCount = projectList.DetailsInfo.Count().ToString();
                }
                //项目相关人员Id
                projectList.Users = new List<UserInfo>();
                var userList = await (from userproject in Db.MsUserToProjects
                                      join user in Db.SysUsers on userproject.YunUserGuid equals user.YunUserGuid
                                      where userproject.YunProjectGuid == projectList.ProjectId && !user.IsDeleted && user.Status == Core.Model.Sys.SysUser.EnumUserStatus.正常
                                      select new UserInfo
                                      {
                                          UserId = userproject.YunUserGuid,
                                          Name = user.Name,
                                          Phone = user.PhoneNumber,
                                          Position = user.Position,
                                          Department = user.Department,
                                          Email = user.Email,
                                          RoleName = user.RoleNameList
                                      }).ToArrayAsync().ConfigureAwait(false);
                foreach (var i in userList)
                {
                    var j = i.CopyTo<UserInfo>();
                    projectList.Users.Add(j);
                    projectList.UserNames = (string.IsNullOrEmpty(projectList.UserNames) ? "" : (projectList.UserNames + ",")) + j.Name;
                }
            }
            return result;
        }

        #region 获取项目下拉选项

        public async Task<List<SelectItem>> GetProjectSelectItem()
        {
            var tt = await
                       (from p in Db.MsProjects
                        join userproject in Db.SysUserProject
                           on p.ProjectId equals userproject.ProjectId into t
                        from lis in t.DefaultIfEmpty()
                        where !p.IsDeleted
                                 && p.Status == MsProject.ProjectStatus.启用
                               //&& !userproject.IsDeleted
                               //&& userproject.UserId == OperatorProvider.Get().Id
                               && (
                                   lis.UserId == OperatorProvider.Get().Id
                                   || OperatorProvider.Get().RoleCode.Contains("cloud_measure_robot_master")
                                 )
                        select new { p.Name, p.ProjectId, p.LastUpdatedDate }).AsNoTracking().ToListAsync().ConfigureAwait(false);
            return tt.OrderByDescending(c => c.LastUpdatedDate).Select(c => new SelectItem(c.Name, c.ProjectId.ToString())).Distinct().ToList();

            //var query = await
            //              (from p in Db.MsProjects
            //               join userproject in Db.SysUserProject
            //               on p.ProjectId equals userproject.ProjectId into t
            //               from lis in t.DefaultIfEmpty()
            //               where !p.IsDeleted
            //                     && p.Status == MsProject.ProjectStatus.启用
            //                   //&& !userproject.IsDeleted
            //                   //&& userproject.UserId == OperatorProvider.Get().Id
            //                   && (
            //                       lis.UserId == OperatorProvider.Get().Id
            //                       || OperatorProvider.Get().RoleCode.Contains("cloud_measure_robot_master")
            //                     )
            //               orderby p.LastUpdatedDate descending
            //               select new SelectItem(p.Name, p.ProjectId.ToString())

            //    ).AsNoTracking().Distinct().ToListAsync().ConfigureAwait(false);
            //return query;
        }

        #endregion 获取项目下拉选项

        #region 获取首页项目统计信息

        public async Task<List<ProjectForHome>> GetProjectForHome()
        {
            //var list = await Query()
            //           .Where(w => !w.IsDeleted)
            //           .Select(w => w.CopyTo<ProjectInfoForHome>())
            //           .ToListAsync();
            var list = await (
                      from up in Db.SysUserProject
                      join p in Db.MsProjects on up.ProjectId equals p.ProjectId
                      where !p.IsDeleted && p.Status == MsProject.ProjectStatus.启用
                      && up.UserId == OperatorProvider.Get().Id
                      select new ProjectInfoForHome
                      {
                          ProjectId = p.ProjectId,
                          Name = p.Name,
                          City = p.City,
                          Town = p.Town,
                          Address = p.Address
                      }
                ).AsNoTracking().ToListAsync().ConfigureAwait(false);

            var groups = list.GroupBy(d => d.City).Select(g => (new ProjectForHome()//这里的details是未分组前的一个list
            {
                Name = g.Key,
                ProjectNum = g.Count(),
                List = g.ToList()
            })).ToList();
            groups.RemoveAll(s => { return String.IsNullOrWhiteSpace(s.Name); });
            return groups;
        }

        /// <summary>
        /// 数据分析-质量分析-分页查询项目整体合格率
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="companyGuid"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetProjectOverallPassRatePage(ProjectOverallPassRatePara condition, Guid companyGuid)
        {
            var query = QueryProjectForChart(condition, companyGuid);
            //var query = QueryProjectPassRate(condition, companyGuid);
            condition.AscDesc = "DESC";
            condition.OrderBy = "ProjectId";
            var projectList = query.ToList();
            ApiPageResult pageResult = new ApiPageResult()
            {
                PageData = projectList,
                Total = query.Count(),
            };
            return pageResult;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 数据分析-质量分析-查询项目信息，图表展示使用
        /// </summary>
        /// <returns></returns>
        private IQueryable<ProjectInfoForHome> QueryProjectForChart(ProjectOverallPassRatePara condition, Guid companyGuid)
        {
            var query = (
                from up in Db.MsUserToProjects
                join p in Db.MsProjects on up.YunProjectGuid equals p.ProjectId
                where !p.IsDeleted && p.Status == MsProject.ProjectStatus.启用
                                   && (up.YunUserGuid == OperatorProvider.Get().Id || p.CompanyGuid == companyGuid)
                                   && (string.IsNullOrEmpty(condition.Name) || p.Name.Contains(condition.Name))
                                   && p.PubliceDate != null
                select new ProjectInfoForHome
                {
                    ProjectId = p.ProjectId,
                    Name = p.Name,
                    City = p.City,
                    Town = p.Town,
                    Address = p.Address,
                    ConstructionPermitNum = p.ConstructionPermitNum,
                    BuildingDetails = p.Details,
                }
            );
            return query;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取项目信息，图表展示使用
        /// </summary>
        /// <returns></returns>
        public async Task<List<ProjectInfoForHome>> GetProjectForChart(MsCondition condition, Guid companyGuid)
        {
            var list = await (
                from up in Db.MsUserToProjects
                join p in Db.MsProjects on up.YunProjectGuid equals p.ProjectId
                where !p.IsDeleted && p.Status == MsProject.ProjectStatus.启用
                                   && (up.YunUserGuid == OperatorProvider.Get().Id || p.CompanyGuid == companyGuid)
                                   && (string.IsNullOrEmpty(condition.ConstructionPermitNum) || p.ConstructionPermitNum == condition.ConstructionPermitNum)
                                   && (string.IsNullOrEmpty(condition.Name) || p.Name.Contains(condition.Name))
                                   && p.PubliceDate != null
                select new ProjectInfoForHome
                {
                    ProjectId = p.ProjectId,
                    Name = p.Name,
                    City = p.City,
                    Town = p.Town,
                    Address = p.Address,
                    ConstructionPermitNum = p.ConstructionPermitNum,
                    BuildingDetails = p.Details,
                }
            ).AsNoTracking().Distinct().ToListAsync().ConfigureAwait(false);

            return list;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 分页获取项目信息，图表展示使用
        /// </summary>
        /// <returns></returns>
        public async Task<List<ProjectInfoForHome>> GetProjectForChartPage(MsProjectPageCondition condition, Guid companyGuid)
        {
            var list = await (
                from up in Db.MsUserToProjects
                join p in Db.MsProjects on up.YunProjectGuid equals p.ProjectId
                where !p.IsDeleted && p.Status == MsProject.ProjectStatus.启用
                                   && (up.YunUserGuid == OperatorProvider.Get().Id || p.CompanyGuid == companyGuid)
                                   && (string.IsNullOrEmpty(condition.ConstructionPermitNum) || p.ConstructionPermitNum == condition.ConstructionPermitNum)
                                   && (string.IsNullOrEmpty(condition.Name) || p.Name.Contains(condition.Name))
                                   && p.PubliceDate != null
                orderby p.CreatedDate descending
                select new ProjectInfoForHome
                {
                    ProjectId = p.ProjectId,
                    Name = p.Name,
                    City = p.City,
                    Town = p.Town,
                    Address = p.Address,
                    ConstructionPermitNum = p.ConstructionPermitNum,
                    BuildingDetails = p.Details,
                }
            ).AsNoTracking().Distinct().ToListAsync().ConfigureAwait(false);

            var result = list.Skip((condition.CurrentPage - 1) * condition.PageSize).Take(condition.PageSize).ToList();  // 分页数据
            return result;
        }

        /// <summary>
        /// 更新项目版本号
        /// </summary>
        /// <param name="projectId"></param>
        public void UpdateProjectVersion(Guid projectId)
        {
            var mpj = this.GetById(projectId);
            if (mpj != null)
            {
                mpj.ProjectVersion = mpj.ProjectVersion + 1;
                mpj.IsCanPull = false;
                this.Update(mpj, x => new { x.ProjectVersion });
            }
        }

        #endregion 获取首页项目统计信息

        public async Task<string> CheckHouseMapForPublice(Guid projectId)
        {
            string sqlstr = "SELECT COUNT(p.\"HouseMapId\") AS num,h.\"HouseMapId\" FROM \"MsHouseMap\" AS h left JOIN \"MsHouseMapPoint\" AS p ON h.\"HouseMapId\" = p.\"HouseMapId\" WHERE h.\"ProjectId\"= @ProjectId and h.\"IsDeleted\" = false GROUP BY h.\"HouseMapId\";";

            NpgsqlParameter para = new NpgsqlParameter("@ProjectId", projectId);
            var list = await PgSqlHelper.QueryListAsync<(long Num, Guid HouseMapId)>(CommandType.Text, sqlstr, false, para);
            if (list == null || list.Count == 0)
                return Localizer["YB0229"].Value;

            foreach (var item in list)
            {
                if (item.Num == 0)
                    return Localizer["YB0230"].Value;
            }
            return string.Empty;
        }

        /// <summary>
        /// 项目发布，判断户型图有没有关联楼栋楼层
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns>错误消息</returns>
        public async Task<string> CheckHouseMapBuildingAndFloorForPublice(Guid projectId)
        {
            string sqlstr = "select h.\"HouseMapId\",count(d.\"HouseMapId\") AS num from \"MsHouseMap\" as h left join \"MsHouseMapDetails\" as d on h.\"HouseMapId\" = d.\"HouseMapId\"  WHERE h.\"ProjectId\"= @ProjectId and h.\"IsDeleted\" = false group by h.\"HouseMapId\"; ";

            NpgsqlParameter para = new NpgsqlParameter("@ProjectId", projectId);
            var list = await PgSqlHelper.QueryListAsync<(Guid HouseMapId, long Num)>(sqlstr, para);
            if (list == null || list.Count == 0)
                return Localizer["YB0229"].Value;

            foreach (var item in list)
            {
                if (item.Num == 0)
                    return Localizer["GX00001"].Value;
            }
            return string.Empty;
        }

        /// <summary>
        /// 作者：黄钧航
        /// FMS-获取所有已有报告数据回传的项目信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<CSMSTaskDtoPart>> GetAllAvailableProjects()
        {
            // 项目设备信息
            var result = await (
               from h in Db.MsProjects
               join c in Db.CSMSTasks on h.ProjectId equals c.ProjectId
               where (!h.IsDeleted && h.IsPullByClient && c.ProjectId != null)
               orderby h.CreatedDate descending
               select new CSMSTaskDtoPart()
               {
                   ProjectName = h.Name,
                   ProjectId = h.ProjectId,
                   DeviceGuid = c.DeviceGuid,
                   TaskId = c.TaskId,
                   BuildingName = c.BuildingName,
                   FloorName = c.FloorName,
               }).AsNoTracking().ToListAsync().ConfigureAwait(false);

            return null;
        }

        /// <summary>
        /// 查询项目列表(包含是否有报告)
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetProjectWithReportList(MsProjectCondition condition)
        {
            var result = condition.CopyTo<ApiPageResult>();
            var queryable = GetMsProjectReportIQueryable(condition);
            result.PageData = queryable.PageOrderBy(condition).Skip((condition.CurrentPage - 1) * condition.PageSize).Take(condition.PageSize).ToList();

            result.Total = queryable.Count();
            return result;
        }

        /// <summary>
        /// 获取IQueryable
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        private IQueryable<MsProjectReportDto> GetMsProjectReportIQueryable(MsProjectCondition condition)
        {
            var user = OperatorProvider.Get();
            var isCompanyAdmin = user.IsCompanyAdmin;
            IQueryable<MsProjectReportDto> queryable;
            if (isCompanyAdmin)
            {
                queryable = (
                from project in Db.MsProjects
                where (!project.IsDeleted && project.Status == MsProject.ProjectStatus.启用
                && (project.CompanyGuid == OperatorProvider.Get().CompanyGuid)
                && (string.IsNullOrEmpty(condition.ProjectName) || project.Name.Contains(condition.ProjectName) || project.ProjectNo.Contains(condition.ProjectName)))
                select new MsProjectReportDto()
                {
                    ProjectId = project.ProjectId,
                    Name = project.Name,
                    CreatedDate = project.CreatedDate,
                    ProjectNo = project.ProjectNo,
                    HasReport = (from cstask in Db.CSMSTasks where (cstask.ProjectId == project.ProjectId && !cstask.IsDeleted) select cstask.TaskId).Count() > 0
                });
            }
            else
            {
                //是否测量员
                var isMeasure = user.RoleNameList.Contains("测量员") || user.RoleNameList.Contains("项目管理员");
                queryable = (
                from project in Db.MsProjects
                join userprojet in Db.MsUserToProjects on project.ProjectId equals userprojet.YunProjectGuid
                where (!project.IsDeleted && project.Status == MsProject.ProjectStatus.启用
                && (userprojet.YunUserGuid == user.Id || user.RoleCode.Contains("cloud_measure_robot_master"))
                && (string.IsNullOrEmpty(condition.ProjectName) || project.Name.Contains(condition.ProjectName) || project.ProjectNo.Contains(condition.ProjectName))
                //如果是测量员，只能看已发布的项目
                && (!isMeasure ? true : (project.PubliceDate.HasValue)))
                select new MsProjectReportDto()
                {
                    ProjectId = project.ProjectId,
                    Name = project.Name,
                    CreatedDate = project.CreatedDate,
                    ProjectNo = project.ProjectNo,
                    HasReport = (from cstask in Db.CSMSTasks where (cstask.ProjectId == project.ProjectId && !cstask.IsDeleted) select cstask.TaskId).Count() > 0
                });
            }

            return queryable;
        }

        /// <summary>
        /// 提供测量服务获取已发布项目
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<List<MeasureServiceProjectDto>> GetPublishProjects(MsProjectCondition condition)
        {
            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter() {ParameterName = "CompanyId", Value = OperatorProvider.Get()?.CompanyGuid },
            };
            var sql = @"SELECT
                            ""ProjectId"",
                            ""Name"",
                            ""CompanyGuid"",
                            ""PubliceDate"",
                            ""ProjectNo"",
                            ""Address""
                        FROM
                            ""MsProject""
                        WHERE
                            ""PubliceDate"" IS NOT NULL
                            AND ""IsDeleted"" = FALSE
                            AND ""Status"" = 0 ";
            var whereSql = condition.Allowlist ? "" : @" AND ""CompanyGuid"" = @CompanyId ";
            sql += whereSql;

            var list = await PgSqlHelper.QueryListAsync<MeasureServiceProjectDto>(CommandType.Text, sql, false, parameters.ToArray());

            return list;
        }

        /// <summary>
        /// 获取工单项目列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetMapProject(WorkOrderMapRequestPageDto condition)
        {
            var user = OperatorProvider.Get();
            IQueryable<WorkOrderMapProjectsResponseDto> queryable;

            if (user.IsCompanyAdmin)
            {
                queryable = (
                                        from project in Db.MsProjects
                                        where (!project.IsDeleted && project.Status == MsProject.ProjectStatus.启用
                                        && (project.CompanyGuid == user.CompanyGuid)
                                        && (string.IsNullOrEmpty(condition.ProjectName) || project.Name.Contains(condition.ProjectName))
                                        && (condition.ProjectId == Guid.Empty || project.ProjectId == condition.ProjectId))
                                        select new WorkOrderMapProjectsResponseDto()
                                        {
                                            ProjectId = project.ProjectId.ToString(),
                                            ProjectName = project.Name,
                                            IsChecked = false,
                                            ProjectNo = project.ProjectNo,
                                        });
                condition.AscDesc = string.IsNullOrEmpty(condition.OrderBy) ? "DESC" : condition.AscDesc;
                condition.OrderBy = string.IsNullOrEmpty(condition.OrderBy) ? "ProjectNo DESC" : $"{condition.OrderBy} {condition.AscDesc}";
                var result = condition.CopyTo<ApiPageResult>();
                result.PageData = await queryable.ToPageList(condition);
                result.Total = condition.Total;
                return result;
            }
            return null;
        }
    }
}